-- G100 2215.5 A

CREATE or replace TYPE ty_settleitemcfg AS OBJECT
(
    BrokerID CHAR(10), -- 经纪公司代码
    ExchangeID CHAR(8), -- 交易所代码
    SettleManagerType CHAR(1), -- 结算配置类型
    SettleManagerID CHAR(32), -- 结算配置代码
    SettleManagerName CHAR(128), -- 结算配置名称
    SettleManagerLevel CHAR(1), -- 结算配置等级
    IsConfig NUMBER(1), -- 是否可配置
    OrderNo CHAR(32), -- 序号
    Memo CHAR(160), -- 说明
    FunctionUrl VARCHAR2(1024), -- 功能链接
    ListID VARCHAR2(1024), -- ListID
    ResultClass VARCHAR2(1024), -- ResultClass
    SettleManagerGroup CHAR(1), -- 模块分组
    OperatorID CHAR(64), -- 操作员代码
    OpDate CHAR(8), -- 操作日期
    OpTime CHAR(8), -- 操作时间
        -- 构造空对象
    CONSTRUCTOR FUNCTION Ty_SettleItemCfg  RETURN SELF AS RESULT, -- 将对象的成员转换成字符串(仅为日志使用)
    MEMBER FUNCTION uf_toString RETURN VARCHAR2
) ;
/

CREATE or replace TYPE BODY ty_settleitemcfg IS
    -- 构造空对象
    CONSTRUCTOR FUNCTION Ty_SettleItemCfg RETURN SELF AS RESULT
    IS
BEGIN
        RETURN;
END;
     -- 将对象的成员转换成字符串(仅为日志使用)
    MEMBER FUNCTION uf_toString  RETURN VARCHAR2
    IS
        l_string VARCHAR2(4000);

BEGIN
        l_string := 'ty_SettleItemCfg(' || 'BrokerID=>' || '''' || trim(BrokerID) || '''' || ',ExchangeID=>' || '''' || trim(ExchangeID) || '''' || ',SettleManagerType=>' || '''' || trim(SettleManagerType) || '''' || ',SettleManagerID=>' || '''' || trim(SettleManagerID) || '''' || ',SettleManagerName=>' || '''' || trim(SettleManagerName) || '''' || ',SettleManagerLevel=>' || '''' || trim(SettleManagerLevel) || '''' || ',IsConfig=>' || '''' || trim(IsConfig) || '''' || ',OrderNo=>' || '''' || trim(OrderNo) || '''' || ',Memo=>' || '''' || trim(Memo) || '''' || ',FunctionUrl=>' || '''' || trim(FunctionUrl) || '''' || ',ListID=>' || '''' || trim(ListID) || '''' || ',ResultClass=>' || '''' || trim(ResultClass) || '''' || ',SettleManagerGroup=>' || '''' || trim(SettleManagerGroup) || '''' || ',OperatorID=>' || '''' || trim(OperatorID) || '''' || ',OpDate=>' || '''' || trim(OpDate) || '''' || ',OpTime=>' || '''' || trim(OpTime) || '''' || ')';
RETURN l_string;
EXCEPTION
        WHEN OTHERS THEN RETURN NULL;
END;

END;
/

CREATE or replace TYPE ty_page AS OBJECT
(
    StartIndex NUMBER(12), -- 开始号码
    EndIndex NUMBER(12), -- 结束号码
    OrderColumns CHAR(160), -- 排序字段
    OrderDirection CHAR(160), -- 排序方向
        -- 构造空对象
    CONSTRUCTOR FUNCTION Ty_Page  RETURN SELF AS RESULT, -- 将对象的成员转换成字符串(仅为日志使用)
    MEMBER FUNCTION uf_toString  RETURN VARCHAR2
) ;
/

CREATE or replace TYPE BODY ty_page IS
    -- 构造空对象
    CONSTRUCTOR FUNCTION Ty_Page  RETURN SELF AS RESULT
    IS
BEGIN
        RETURN;
END;
     -- 将对象的成员转换成字符串(仅为日志使用)
    MEMBER FUNCTION uf_toString  RETURN VARCHAR2
    IS
        l_string VARCHAR2(4000);

BEGIN
        l_string := 'ty_Page(' || 'StartIndex=>' || NVL(to_char(StartIndex), 'NULL') || ',EndIndex=>' || NVL(to_char(EndIndex), 'NULL') || ',OrderColumns=>' || '''' || trim(OrderColumns) || '''' || ',OrderDirection=>' || '''' || trim(OrderDirection) || '''' || ')';
RETURN l_string;
EXCEPTION
        WHEN OTHERS THEN RETURN NULL;
END;

END;
/

CREATE or replace  TYPE ty_settleitemcheck AS OBJECT
(
    TradingDay CHAR(8), -- 交易日
    BrokerID CHAR(10), -- 经纪公司代码
    ExchangeID CHAR(8), -- 交易所代码
    SettleManagerType CHAR(1), -- 结算配置类型
    SettleManagerID CHAR(32), -- 结算配置代码
    SettleManagerName CHAR(128), -- 结算配置名称
    SettleManagerLevel CHAR(1), -- 结算配置等级
    CheckResult NUMBER(1), -- 核对结果
    CheckResultMemo VARCHAR2(1024), -- 核对结果说明
    OperatorID CHAR(64), -- 操作员代码
    OpDate CHAR(8), -- 操作日期
    OpTime CHAR(8), -- 操作时间
        -- 构造空对象
    CONSTRUCTOR FUNCTION Ty_SettleItemCheck  RETURN SELF AS RESULT, -- 将对象的成员转换成字符串(仅为日志使用)
    MEMBER FUNCTION uf_toString  RETURN VARCHAR2
) ;
/

CREATE or replace  TYPE BODY ty_settleitemcheck IS
    -- 构造空对象
    CONSTRUCTOR FUNCTION Ty_SettleItemCheck  RETURN SELF AS RESULT
    IS
BEGIN
        RETURN;
END;
     -- 将对象的成员转换成字符串(仅为日志使用)
    MEMBER FUNCTION uf_toString  RETURN VARCHAR2
    IS
        l_string VARCHAR2(4000);
BEGIN
        l_string := 'ty_SettleItemCheck(' || 'TradingDay=>' || '''' || trim(TradingDay) || '''' || ',BrokerID=>' || '''' || trim(BrokerID) || '''' || ',ExchangeID=>' || '''' || trim(ExchangeID) || '''' || ',SettleManagerType=>' || '''' || trim(SettleManagerType) || '''' || ',SettleManagerID=>' || '''' || trim(SettleManagerID) || '''' || ',SettleManagerName=>' || '''' || trim(SettleManagerName) || '''' || ',SettleManagerLevel=>' || '''' || trim(SettleManagerLevel) || '''' || ',CheckResult=>' || '''' || trim(CheckResult) || '''' || ',CheckResultMemo=>' || '''' || trim(CheckResultMemo) || '''' || ',OperatorID=>' || '''' || trim(OperatorID) || '''' || ',OpDate=>' || '''' || trim(OpDate) || '''' || ',OpTime=>' || '''' || trim(OpTime) || '''' || ')';
RETURN l_string;
EXCEPTION
        WHEN OTHERS THEN RETURN NULL;
END;

END;
/






CREATE TABLE t_settleitemcfg (
                                 brokerid char(10) NOT NULL,
                                 exchangeid char(8) NOT NULL,
                                 settlemanagertype char(1) NOT NULL,
                                 settlemanagerid char(32) NOT NULL,
                                 settlemanagername char(128) NOT NULL,
                                 settlemanagerlevel char(1) NOT NULL,
                                 isconfig number(1) NOT NULL,
                                 orderno char(32) NOT NULL,
                                 memo char(160),
                                 functionurl varchar(1024),
                                 listid varchar(1024),
                                 resultclass varchar(1024),
                                 settlemanagergroup char(1),
                                 operatorid char(64),
                                 opdate char(8),
                                 optime char(8)
);

INSERT INTO t_settleitemcfg (brokerid,exchangeid,settlemanagertype,settlemanagerid,settlemanagername,settlemanagerlevel,isconfig,orderno,memo,functionurl,listid,resultclass,settlemanagergroup,operatorid,opdate,optime)
VALUES ('8000','INE','3','A_INE_CHECK_T','能源中心成交核对','3',1,'3710','检查成交表中能源中心成交记录是否与导入交易所成交记录一致','成交单管理,com.sfit.dorado.view.settlement.deal.Trade.d,交易所结算文件上传,com.sfit.dorado.view.settlement.SettlementFileUploadFile.d,成交明细查询,com.sfit.dorado.view.infoquery.investorinfo.QueryInvestorTrade.d','ExDetailCheckDataGrid,ResultChkINESetBrokerTra','com.sfit.model.settlement.ResultChkINESetBrokerTra','1','0000.0000_admin','20241202','13:19:23');

create table t_page(
                       StartIndex NUMBER(12), -- 开始号码
                       EndIndex NUMBER(12), -- 结束号码
                       OrderColumns CHAR(160), -- 排序字段
                       OrderDirection CHAR(160) --排序方向
);

insert into t_page values('12.23','22','成交单管理,com.￥e69da5e887aa62616964757a686964616f31333433636136#@','11交单管理,com.￥e69da5e8');

create table t_settleitemcheck(
                                  TradingDay CHAR(8), -- 交易日
                                  BrokerID CHAR(10), -- 经纪公司代码
                                  ExchangeID CHAR(8), -- 交易所代码
                                  SettleManagerType CHAR(1), -- 结算配置类型
                                  SettleManagerID CHAR(32), -- 结算配置代码
                                  SettleManagerName CHAR(128), -- 结算配置名称
                                  SettleManagerLevel CHAR(1), -- 结算配置等级
                                  CheckResult NUMBER(1), -- 核对结果
                                  CheckResultMemo VARCHAR2(1024), -- 核对结果说明
                                  OperatorID CHAR(64), -- 操作员代码
                                  OpDate CHAR(8), -- 操作日期
                                  OpTime CHAR(8)
);

insert into t_settleitemcheck values('20241211','8888','INE','1','sad,asd.￥e','SettleManagerName','2',1,'单管理,com.￥e69da','0000.0000_admin','20241202','13:19:23');

create or replace package pkgi_settlemgrt as
  PROCEDURE up_checksettlemanager(
     i_TytSettleItemCfg IN TY_SETTLEITEMCFG  --结算对象
    ,i_Ty_Page IN TY_PAGE  --分页信息
    ,io_Ty_SettleItemCheck OUT TY_SETTLEITEMCHECK  --返回的核对信息
    ,i_chOperatorID IN CHAR  --操作源代码
    ,o_nPageCount OUT   NUMBER  --返回页码
    ,o_Cursor OUT   sys_refCURSOR  --返回游标
    ,o_nRetCode OUT   NUMBER  --返回码
    ,o_varRetMsg OUT   VARCHAR2  --返回信息
  );
end pkgi_settlemgrt;
/

create or replace package body pkgi_settlemgrt as
  PROCEDURE up_checksettlemanager(
     i_TytSettleItemCfg IN TY_SETTLEITEMCFG  --结算对象
    ,i_Ty_Page IN TY_PAGE  --分页信息
    ,io_Ty_SettleItemCheck OUT TY_SETTLEITEMCHECK  --返回的核对信息
    ,i_chOperatorID IN CHAR  --操作源代码
    ,o_nPageCount OUT   NUMBER  --返回页码
    ,o_Cursor OUT   sys_refCURSOR  --返回游标
    ,o_nRetCode OUT   NUMBER  --返回码
    ,o_varRetMsg OUT   VARCHAR2  --返回信息
  ) as
   l_cnt int;
begin
    l_cnt := 1;
    dbms_output.put_line('BrokerID is '||i_TytSettleItemCfg.BrokerID);
    dbms_output.put_line('Memo is '||i_TytSettleItemCfg.Memo);
    dbms_output.put_line('FunctionUrl is '||i_TytSettleItemCfg.FunctionUrl);
    dbms_output.put_line('ListID is '||i_TytSettleItemCfg.ListID);
    dbms_output.put_line('ResultClass is '||i_TytSettleItemCfg.ResultClass);
    dbms_output.put_line('OperatorID is '||i_TytSettleItemCfg.OperatorID);
    dbms_output.put_line('OpDate is '||i_TytSettleItemCfg.OpDate);
    dbms_output.put_line('OpTime is '||i_TytSettleItemCfg.OpTime);
    --io_Ty_SettleItemCheck.TradingDay := '20241212';
    --io_Ty_SettleItemCheck.BrokerID := '8888';
    --io_Ty_SettleItemCheck.ExchangeID := 'INE';
    --io_Ty_SettleItemCheck.SettleManagerType := '1';
    --io_Ty_SettleItemCheck.SettleManagerID := 'SettleManagerID';
    --io_Ty_SettleItemCheck.SettleManagerName := 'SettleManagerName';
    --io_Ty_SettleItemCheck.SettleManagerLevel := '1';
    --io_Ty_SettleItemCheck.CheckResult := 2;
    --io_Ty_SettleItemCheck.CheckResultMemo := '20241212';
    --io_Ty_SettleItemCheck.OperatorID := '0000.0000admin';
    --io_Ty_SettleItemCheck.OpDate := '20241212';
    --io_Ty_SettleItemCheck.OpDate := '12:21:22';
select Ty_SettleItemCheck(TradingDay,BrokerID,ExchangeID,SettleManagerType,SettleManagerID,SettleManagerName,SettleManagerLevel,CheckResult,CheckResultMemo,OperatorID,OpDate,OpTime)
into io_Ty_SettleItemCheck
from t_settleitemcheck where rownum = 1;
open o_Cursor for select 'cursor_1','name' from dual union select 'cursor_2','name2' from dual;
end up_checksettlemanager;
end pkgi_settlemgrt;
/

--调用
SET serveroutput =on;

declare
l_ty_SETTLEITEMCFG TY_SETTLEITEMCFG;
  io_Ty_SettleItemCheck TY_SETTLEITEMCHECK;
  o_npagecount number;
  o_cursor sys_refCURSOR;
  o_nRetCode number;
  o_varRetMsg varchar2(200);
begin
  --向ty插入数据
select TY_SETTLEITEMCFG(BrokerID,ExchangeID,SettleManagerType,SettleManagerID,SettleManagerName,SettleManagerLevel,IsConfig,OrderNo,Memo,FunctionUrl,ListID,ResultClass,SettleManagerGroup,OperatorID,OpDate,OpTime)
into l_ty_SETTLEITEMCFG
from t_settleitemcfg where rownum = 1;
pkgi_settlemgrt.up_checksettlemanager(
    l_ty_SETTLEITEMCFG,
    null,
    io_Ty_SettleItemCheck,
    '0000.admin',
    o_npagecount,
    o_cursor,
    o_nRetCode,
    o_varRetMsg
  );

  dbms_output.put_line('ty_settleitemcheck.TradingDay is '||io_Ty_SettleItemCheck.TradingDay);
  dbms_output.put_line('ty_settleitemcheck.BrokerID is '||io_Ty_SettleItemCheck.BrokerID);
  dbms_output.put_line('ty_settleitemcheck.ExchangeID is '||io_Ty_SettleItemCheck.ExchangeID);
  dbms_output.put_line('ty_settleitemcheck.SettleManagerType is '||io_Ty_SettleItemCheck.SettleManagerType);
  dbms_output.put_line('ty_settleitemcheck.SettleManagerID is '||io_Ty_SettleItemCheck.SettleManagerID);
  dbms_output.put_line('ty_settleitemcheck.SettleManagerName is '||io_Ty_SettleItemCheck.SettleManagerName);
  dbms_output.put_line('ty_settleitemcheck.SettleManagerLevel is '||io_Ty_SettleItemCheck.SettleManagerLevel);
  dbms_output.put_line('ty_settleitemcheck.CheckResult is '||io_Ty_SettleItemCheck.CheckResult);
  dbms_output.put_line('ty_settleitemcheck.CheckResultMemo is '||io_Ty_SettleItemCheck.CheckResultMemo);
  dbms_output.put_line('ty_settleitemcheck.OperatorID is '||io_Ty_SettleItemCheck.OperatorID);
  dbms_output.put_line('ty_settleitemcheck.OpDate is '||io_Ty_SettleItemCheck.OpDate);
  dbms_output.put_line('ty_settleitemcheck.OpTime is '||io_Ty_SettleItemCheck.OpTime);
end;
/









